﻿#include "myexcel.h"

#include <QColor>
#include <QDir>
#include <QFile>
//Application(Excel)--Workbooks(Workbook)--Worksheet(Worksheet)--表格单元。

MyExcel::MyExcel(QObject *parent) : QObject(parent)
{
    excel = NULL;
}

//打开文件，返回id
int MyExcel::openFile(QString filename)
{
    if(excel==NULL)
    {
        excel = new QAxObject("Excel.Application");
        excel->dynamicCall("SetVisible(bool)", true); //true 表示操作文件时可见，false表示为不可见
    }

    workbooks = excel->querySubObject("WorkBooks");
    QAxObject *book = workbooks->querySubObject("Open(QString&)", filename);
    //QAxObject *workbook = excel->querySubObject("ActiveWorkBook");
    QAxObject *worksheets = book->querySubObject("WorkSheets");
    QAxObject *sheet = worksheets->querySubObject("Item(int)", 1);//获取第n个工作表

    QAxObject * usedrange = sheet->querySubObject("UsedRange");//获取该sheet的数据范围（可以理解为有数据的矩形区域）
    QAxObject * rows = usedrange->querySubObject("Rows");
    int rowcnt = rows->property("Count").toInt();//行数
    QAxObject * columns = usedrange->querySubObject("Columns");
    int colcnt = columns->property("Count").toInt();//列数

    workbook.append(book);
    worksheet.append(sheet);
    rowCnt.append(rowcnt);
    colCnt.append(colcnt);
    int id=workbook.length()-1;
    return id;
}


void MyExcel::saveFile(int id)
{
    workbook[id]->dynamicCall("Save()");
}

void MyExcel::closeFile(int id)
{
    workbook[id]->dynamicCall("Close()");
    workbook[id]=NULL;
    worksheet[id]=NULL;
    int b=0;
    foreach (QAxObject *var, workbook)
    {
        if(var != NULL)
            b++;
    }
    if(b==0)
    {
        excel->dynamicCall("Quit()");
        delete excel;
        excel = NULL;
    }
}
void MyExcel::selectSheet(int no,int id)
{
    QAxObject *worksheets = workbook[id]->querySubObject("WorkSheets");
    QAxObject *sheet = worksheets->querySubObject("Item(int)", no);//获取第n个工作表
    worksheet[id]=sheet;
}

bool MyExcel::copyFile(QString fromFile ,QString toFile, bool coverFileIfExist)
{
    toFile.replace("\\","/");
    if (fromFile == toFile){
        return true;
    }
    if (!QFile::exists(fromFile)){
        return false;
    }
    QDir *createfile     = new QDir;
    bool exist = createfile->exists(toFile);
    if (exist){
        if(coverFileIfExist){
            createfile->remove(toFile);
        }
    }//end if

    if(!QFile::copy(fromFile, toFile))
    {
        return false;
    }
    return true;
}

QString MyExcel::getCellValue(int r,int c,int id)
{
    QAxObject *cell =worksheet[id]->querySubObject("Cells(int,int)", r, c);
    QString value = cell->property("Value").toString();  //获取单元格内容
    return value;
}

void MyExcel::setCellValue(int r,int c,QString v,int id)
{
    QAxObject *cell =worksheet[id]->querySubObject("Cells(int,int)", r, c);
    cell->setProperty("Value", v);   //设置单元格值
}

void MyExcel::setCellValue(int r,int c,double v,int id)
{
    QAxObject *cell =worksheet[id]->querySubObject("Cells(int,int)", r, c);
    cell->setProperty("Value", v);   //设置单元格值
}

void MyExcel::deleteRow(int r,int id)
{
    QAxObject *cell = worksheet[id]->querySubObject("Rows(int)",r);//获取选定的行
    if ( cell )
    {
        cell->dynamicCall("Delete()"); //修改所选行
    }
}

void MyExcel::setTextRed(int r,int c,int id)
{
    QAxObject *cell =worksheet[id]->querySubObject("Cells(int,int)", r, c);
    QAxObject *font = cell->querySubObject("Font");  //获取单元格字体
    font->setProperty("Color", QColor(255, 0, 0));  //设置单元格字体颜色（红色）
}

//merge_cell为要合并的单元格，如：A1:A2或B2:C2
void MyExcel::mergeCell(QString merge_cell,int id)
{
    QAxObject *merge_range =worksheet[id]->querySubObject("Range(const QString&)",merge_cell);
    merge_range->setProperty("HorizontalAlignment",-4131);//左对齐（xlLeft）：-4131   居中（xlCenter）：-4108  右对齐（xlRight）：-4152
    merge_range->setProperty("VerticalAlignment", -4108); //上对齐（xlTop）-4160 居中（xlCenter）：-4108  下对齐（xlBottom）：-4107
    merge_range->setProperty("WrapText", true);
    merge_range->setProperty("MergeCells", true);  //合并单元格
    //merge_range->setProperty("MergeCells", false);  //拆分单元格
}
